package org.com.system.util.sql;

import org.apache.commons.lang3.StringUtils;
import org.com.system.util.model.ActionValues;
import org.com.system.util.model.Page;
import org.com.system.util.utils.ModelUtils;
import org.com.system.util.utils.StrUtils;

public class QuerySQLGener {

	private static String db="mysql";
	
	public static String getSQL(String id,Class<?> model){
		String tableName=SQLUtils.getTableName(model);
		return StrUtils.out("\tSELECT \n\t\t"+StringUtils.join(SQLUtils.getColumnNames(model),",")+" \n\tFROM "+tableName+" \n\tWHERE ID='"+id+"'");
	}
	
    public static String getSQL(Object bean,Class<?> model){
    	StringBuffer stber=new StringBuffer();
    	if(StrUtils.isNotEmpty(ModelUtils.getFieldValue("id", bean))){
    	    stber.append(getSQL(ModelUtils.getFieldValue("id", bean), model) );
    	}
    	return stber.toString();
	}
    
    public static String getSQL(ActionValues<String,Object> values,Class<?> model){
    	String tableName=SQLUtils.getTableName(model);
    	StringBuffer stber=new StringBuffer("\tSELECT \n\t\t"+StringUtils.join(SQLUtils.getColumnNames(model),",")+" \n\tFROM "+tableName);
    	SQLGener.addWhereColumnValues(stber, values, model);
    	return StrUtils.out(stber.toString());
	}
	
	public static String listSQL(Class<?> model){
		String tableName=SQLUtils.getTableName(model);
		return StrUtils.out("\tSELECT \n\t\t"+StringUtils.join(SQLUtils.getColumnNames(model),",")+" \n\tFROM "+tableName);
	}
	
	public static String listSQL(Class<?> model,Page page){
		String tableName=SQLUtils.getTableName(model);
		StringBuffer stber=new StringBuffer();
		if(db.equals("mysql")){
			stber.append("\tSELECT \n\t\t");
			stber.append(StringUtils.join(SQLUtils.getColumnNames(model),","));
			stber.append(" \n\tFROM ");
			stber.append(tableName);
			stber.append(" \n ");
			stber.append(" ORDER BY ");
			stber.append(SQLUtils.addUnderline(page.getOrderby())+" "+page.getOrder());
			stber.append(" \n ");
			stber.append(" LIMIT "+(page.getPageNow()<=1?0:(page.getPageNow()-1)*page.getPageSize())+","+page.getPageSize());
		}else if(db.equals("oracle")){
			stber.append("\tSELECT \n\t\t");
			stber.append(StringUtils.join(SQLUtils.getColumnNames(model),","));
			stber.append(" \n\tFROM ");
			stber.append(tableName);
			stber.append(" \n ");
			stber.append(" WHERE ROWID IN (");
			stber.append(" \n ");
			stber.append("    SELECT RID FROM "+tableName+"( ");
			stber.append("        SELECT ROWNUM RN,RID FROM "+tableName+"( \n");
			stber.append("            SELECT ROWID RID,"+StringUtils.join(SQLUtils.getColumnNames(model),",")+" \n FROM "+tableName+" \n ORDER BY "+SQLUtils.addUnderline(page.getOrderby())+" "+page.getOrder());
			stber.append(" \n ) WHERE ROWNUM< "+page.getPageNow()*page.getPageSize());
			stber.append(" \n ) ");
			stber.append(" \n WHERE RN > "+(page.getPageNow()<=1?0:(page.getPageNow()-1)*page.getPageSize()));
			stber.append(" ) \n");
			stber.append(" ORDER BY ");
			stber.append(SQLUtils.addUnderline(page.getOrderby())+" "+page.getOrder());
		}else if(db.equals("sqlserver")){
			
		}
		return StrUtils.out(stber.toString());
	}
	
	public static String listSQL(ActionValues<String,Object> values,Class<?> model){
		String tableName=SQLUtils.getTableName(model);
		StringBuffer stber=new StringBuffer();
		stber.append("\tSELECT \n\t\t");
		stber.append(StringUtils.join(SQLUtils.getColumnNames(model),","));
		stber.append(" \n\tFROM ");
		stber.append(tableName);
		SQLGener.addWhereColumnValues(stber, values, model);
		return StrUtils.out(stber.toString());
	}
	
	public static String listSQL(ActionValues<String,Object> values,Class<?> model,Page page){
		String tableName=SQLUtils.getTableName(model);
		StringBuffer stber=new StringBuffer();
		if(db.equals("mysql")){
			stber.append(" SELECT \n\t");
			stber.append(StringUtils.join(SQLUtils.getColumnNames(model),","));
			stber.append(" \n\tFROM ");
			stber.append(tableName);
			stber.append(" \n ");
			SQLGener.addWhereColumnValues(stber, values, model);
			stber.append(" \n ORDER BY ");
			stber.append(SQLUtils.addUnderline(page.getOrderby())+" "+page.getOrder());
			stber.append(" LIMIT "+(page.getPageNow()<=1?0:(page.getPageNow()-1)*page.getPageSize())+","+page.getPageSize());
		}else if(db.equals("oracle")){
			stber.append(" SELECT \n\t");
			stber.append(StringUtils.join(SQLUtils.getColumnNames(model),","));
			stber.append(" \n\tFROM ");
			stber.append(tableName);
			stber.append(" WHERE ROWID IN (");
			stber.append(" SELECT RID FROM "+tableName+"( ");
			stber.append(" SELECT ROWNUM RN,RID FROM "+tableName+"( ");
			stber.append(" SELECT ROWID RID,"+StringUtils.join(SQLUtils.getColumnNames(model),",")+" FROM "+tableName);
			SQLGener.addWhereColumnValues(stber, values, model);
			stber.append(" ORDER BY "+SQLUtils.addUnderline(page.getOrderby())+" "+page.getOrder());
			stber.append(" ) WHERE ROWNUM< "+page.getPageNow()*page.getPageSize());
			stber.append(" ) ");
			stber.append(" WHERE RN > "+(page.getPageNow()<=1?0:(page.getPageNow()-1)*page.getPageSize()));
			stber.append(" ) ");
			stber.append(" ORDER BY ");
			stber.append(SQLUtils.addUnderline(page.getOrderby())+" "+page.getOrder());
		}else if(db.equals("sqlserver")){
			
		}
		return StrUtils.out(stber.toString());
	}
	
}
